#! /bin/bash

if [[ $1 == "test" ]]
then
  PSQL="psql --username=postgres --dbname=worldcuptest -t --no-align -c"
else
  PSQL="psql --username=freecodecamp --dbname=worldcup -t --no-align -c"
fi

# Do not change code above this line. Use the PSQL variable above to query your database.
echo $($PSQL "TRUNCATE games, teams")

# read file
cat games.csv |  while IFS="," read year round winner opponent winner_goals opponent_goals 
do
  # except line 1
  if [[ $winner != "winner" ]]
  then
    # teams
    # winner
    # team id exsit no insert
    WINNER_TRAM_ID=$($PSQL "select team_id from teams where name='$winner'")
    
    if [[ -z $WINNER_TRAM_ID ]]
    then 
      # insert teams
      INSERT_WINNER_TEAM_ID=$($PSQL "insert into teams(name) values('$winner')")
      if [[ $INSERT_WINNER_TEAM_ID == 'INSERT 0 1' ]]
      then
         echo "insert winner teams name: $winner"
      fi
    fi
    
    # opponent
    # team id exsit no insert
    OPPONENT_TRAM_ID=$($PSQL "select team_id from teams where name='$opponent'")
    
    if [[ -z $OPPONENT_TRAM_ID ]]
    then 
      # insert teams
      INSERT_OPPONENT_TEAM_ID=$($PSQL "insert into teams(name) values('$opponent')")
      if [[ $INSERT_OPPONENT_TEAM_ID == 'INSERT 0 1' ]]
      then
         echo "insert opponent teams name: $winner"
      fi
    fi

    #  games
    GAMES_WINNER_ID=$($PSQL "select team_id from teams where name='$winner'")
    GAMES_OPPONENT_ID=$($PSQL "select team_id from teams where name='$opponent'")
    
    INSERT_GAMES=$($PSQL "insert into games(year,winner_id,opponent_id,winner_goals,opponent_goals,round) values('$year','$GAMES_WINNER_ID','$GAMES_OPPONENT_ID',$winner_goals,$opponent_goals,'$round')")
    if [[ $INSERT_OPPONENT_TEAM_ID == 'INSERT 0 1' ]]
    then
         echo "insert games succ"
    fi
  fi
done


 
